package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.thread.MainWorker;

public class DBJobStatus {
	public static String dbname = DBCommon.dbname ;
	public static String dbuser = DBCommon.dbuser ;
	public static String dbpassword = DBCommon.dbpassword ;
	public static String dbur1 = DBCommon.dbur1;
	
	static Logger logger = Logger.getLogger(DBJobStatus.class);  
	
	public static boolean insertLog(String jobid, String status, String info) {
		boolean rets = false;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
						String nowDate = sdf.format(new Date()) ;
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "insert into publish_job_log(jobid,status,info) values("+jobid+","+status+",'"+info+"') ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						sql.execute(sqlstr);
						// System.out.println(set.next());
						rets = true;
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static boolean insertStatusJob(String Settingid) {
		boolean rets = false;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
						String nowDate = sdf.format(new Date()) ;
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "insert into publish_job_status(frequency,number,renewnum,Settingid,finishednum) values('H', 1, 24,'"+Settingid+"',0) ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						sql.execute(sqlstr);
						// System.out.println(set.next());
						rets = true;
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	
	public static com.alibaba.fastjson.JSONArray getFilterJob() {
		com.alibaba.fastjson.JSONArray rets = new com.alibaba.fastjson.JSONArray();
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error("" + e) ;
					}
					try {
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "SELECT id,frequency,number,renewnum,Settingid,finishednum,time,date_format(lastupdatetime, '%Y-%m-%d %H:%i:%s') from publish_job_status where 1=1 ";
						
						sqlstr += "order by ID desc " ;
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						ResultSet set = sql.executeQuery(sqlstr);
						// System.out.println(set.next());

						while (set.next()) {
							com.alibaba.fastjson.JSONObject cObj = new com.alibaba.fastjson.JSONObject();
							int id = set.getInt(1);
							String frequency = set.getString(2);
							int number = set.getInt(3);
							int renewnum = set.getInt(4);
							int Settingid = set.getInt(5);
							int finishednum = set.getInt(6);
							String time = set.getString(7);
							String lastupdatetime = set.getString(8);
							
							

							cObj.put("id", id);
							cObj.put("frequency", frequency);
							cObj.put("number", number);
							cObj.put("renewnum", renewnum);
							cObj.put("Settingid", Settingid);
							cObj.put("finishednum", finishednum);
							cObj.put("time", time);
							cObj.put("lastupdatetime", lastupdatetime);
							
							
							rets.add(cObj);
						}
						set.close();
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static int getCyclte(String cyclename) {
		int rets = -1;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "SELECT cycleValue from publish_cycle where 1=1  and status='"+cyclename+"' ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						ResultSet set = sql.executeQuery(sqlstr);
						// System.out.println(set.next());

						while (set.next()) {
							rets = set.getInt(1);
						}
						set.close();
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static boolean updateSettingStatus(String settingids, String status) {
		boolean rets = false;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
						String nowDate = sdf.format(new Date()) ;
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "update publish_setting set status='"+status+"',updatetime='"+nowDate+"'  where SettingId in ("+settingids+") ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						sql.execute(sqlstr);
						// System.out.println(set.next());
						rets = true;
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static boolean updateJobTimes(String jobid) {
		boolean rets = false;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
						logger.error(e+"") ;
					}
					try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
						String nowDate = sdf.format(new Date()) ;
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "update publish_job_status set finishednum=finishednum+1,lastupdatetime='"+nowDate+"'  where ID="+jobid+" ";
						System.out.println(sqlstr);
						logger.info(sqlstr) ;
						sql.execute(sqlstr);
						// System.out.println(set.next());
						rets = true;
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						logger.error(el+"") ;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
					logger.error(e+"") ;
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
				logger.error(e+"") ;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static String insertResult(String username , String loginType, String netType , String result, String type, String SettingId, String  status ) {
		String rets = null ;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "insert into publish_result(username , loginType, netType , result, type , SettingId, status ) values('"+username+"','"+loginType+"','"+netType+"','"+result+"','"+type+"','"+SettingId+"','"+status+"') ";
						System.out.println(sqlstr);
						sql.executeUpdate(sqlstr, Statement.RETURN_GENERATED_KEYS);
						// System.out.println(set.next());
						ResultSet set = sql.getGeneratedKeys(); 

						if (set.next()) {
							int keyValue = set.getInt(1); 
							rets = keyValue+"" ;
						}
						set.close();
						conn.close();

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = null;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	
	public static String insertCode(String netType, String publishtype, String codetype, String Status, String Settingid, String photos) {
		String rets = null ;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "insert into publish_code(netType, publishtype, photo, codetype, Status, Setting_id) values('"+netType+"','"+publishtype+"','"+photos+"','"+codetype+"','"+Status+"','"+Settingid+"') ";
						System.out.println(sqlstr);
						sql.executeUpdate(sqlstr, Statement.RETURN_GENERATED_KEYS);
						// System.out.println(set.next());
						ResultSet set = sql.getGeneratedKeys(); 

						if (set.next()) {
							int keyValue = set.getInt(1); 
							rets = keyValue+"" ;
						}
						set.close();
						conn.close();

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = null;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}

	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
//		DB.queryCode("15");
	}

}
